<?php


namespace app\system\controller;


use app\common\model\AreaModel;
use app\common\model\CnzzModel;
use app\common\model\ShopOrderModel;
use app\common\model\UserModel;
use org\net\Cnzz;
use org\util\TreeUtil;
use think\Db;
use think\Request;

class StatisticsController extends BaseController
{
    private $orderModel;
    public function __construct(Request $request)
    {
        parent::__construct($request);
        $this->orderModel = new ShopOrderModel();
    }
    //主页统计
    public function mainStats(){
        //今日下单
        $result = $this->orderModel->field("count(*) as today_total,IFNULL(SUM(pay_price-refund_amount),0) as today_price")
            ->whereTime('create_time','today')
            ->where('site_id',$this->getSiteId())
            ->find();
        $statics['today_total'] = $result['today_total'];
        $statics['today_price'] = $result['today_price'];
        //昨日下单
        $result = $this->orderModel->field("count(*) as yesterday_total,IFNULL(SUM(pay_price-refund_amount),0) as yesterday_price")
            ->whereTime('create_time','yesterday')
            ->where('site_id',$this->getSiteId())
            ->find();
        $statics['yesterday_total'] = $result['yesterday_total'];
        $statics['yesterday_price'] = $result['yesterday_price'];
        //本月下单
        $result = $this->orderModel->field("count(*) as month_total,IFNULL(SUM(pay_price-refund_amount),0) as month_price")
            ->whereTime('create_time','month')
            ->where('site_id',$this->getSiteId())
            ->find();
        $statics['month_total'] = $result['month_total'];
        $statics['month_price'] = $result['month_price'];
        //上月下单
        $result = $this->orderModel->field("count(*) as last_month_total,IFNULL(SUM(pay_price-refund_amount),0) as last_month_price")
            ->whereTime('create_time','last month')
            ->where('site_id',$this->getSiteId())
            ->find();
        $statics['last_month_total'] = $result['last_month_total'];
        $statics['last_month_price'] = $result['last_month_price'];
        return $statics;
    }
    /**
     * 综合数据指标
     * @return mixed
     */
    public function shopstats(){
        //订单来源
        $source = [1=>'手机端',2=>'电脑端',3=>'微信端'];
        $orderFrom = $this->orderModel->field("order_from,count(*) as total")->where('site_id',$this->getSiteId())->group('order_from')->select()->toArray();
        $arr = complet_arr($orderFrom,'order_from','total',1,3);
        $orderFromStatic=[];
        foreach ($arr as $v){
            $orderFromStatic[]=['value'=>$v['total'],'name'=>$source[$v['order_from']]];
        }
        $pieData=[
            "title"=>[
                "text"=>"订单来源统计",
            ],
            "legend"=>[
                "data"=>array_values($source)
            ],
            "series"=>[
                "name"=>"订单来源",
                "data"=>$orderFromStatic
            ]
        ];
        //订单区域分布
        $orderArea = $this->orderModel->field("prov_id,count(*) as total,sum(pay_price-refund_amount) as total_price")->where('site_id',$this->getSiteId())->group('prov_id')->select()->toArray();
        $areaModel = new AreaModel();
        $provList = $areaModel->where('pid',0)->column('area_name','id');
        foreach ($provList as $k=>$v){
            $provList[$k] = str_replace('省','',$v);
        }
        $orderAreaStatic1=[];
        $orderAreaStatic2=[];
        foreach($orderArea as $v){
            $orderAreaStatic1[]=['value'=>$v['total'],'name'=>$provList[$v['prov_id']]];
            $orderAreaStatic2[]=['value'=>$v['total_price'],'name'=>$provList[$v['prov_id']]];
        }
        $mapData=[
            "title"=>[
                "text"=>"订单区域分布图",
            ],
            "legend"=>[
                "data"=>['下单量区域分布','下单金额区域分布']
            ],
            "series"=>[
                ["name"=>"下单量区域分布", "data"=>$orderAreaStatic1],
                ["name"=>"下单金额区域分布", "data"=>$orderAreaStatic2]
            ]
        ];
        //会员购买率
        $statics = $this->orderModel->field("count(*) as total,sum(pay_price-refund_amount) as total_price")->where('order_status',2)->where('site_id',$this->getSiteId())->find()->toArray();
        $statics['order_total'] = $this->orderModel->where('site_id',$this->getSiteId())->count();
        $userModel = new UserModel();
        $statics['member_total'] = $userModel->where('utype','member')->count();
        $statics['member_rate'] = $statics['order_total']>0?round(($statics['total']/$statics['order_total'])*100).'%':0;
        $statics['total_geval'] = Db::table('shop_goods_geval')->count();
        $statics['total_goods'] = Db::table('shop_goods_sku')->count();
        $this->assign('statics',$statics);
        $this->assign('mapData',json_encode($mapData));
        $this->assign('pieData',json_encode($pieData));
        return $this->fetch();
    }

    /**
     * 订单数据统计
     * @return mixed
     */
    public function ordercount(){
        $data = $this->request->param();
        $timeType = empty($data['time_type'])?0:$data['time_type'];
        $bdate = empty($data['bdate'])?"2017-07-01":$data['bdate'];
        $edate = empty($data['edate'])?date("Y-m-d",time()):$data['edate'];
        $query['bdate'] = $bdate;
        $query['edate'] = $edate;
        /**
        0总订单
        3积分
        4集采
        5 竞拍
         */
        $type = 'line';
        switch ($timeType)
        {
            case 0:
                $horizontal[] = '总订单金额统计';
                $info[][] = $this->orderModel->where('order_status',2)->sum('pay_price');//总订单
                $info[][] = $this->orderModel->where(['order_type'=>3,'order_status'=>2])->sum('pay_price');//积分
                $info[][] = $this->orderModel->where(['order_type'=>4,'order_status'=>2])->sum('pay_price');//集采
                $info[][] = $this->orderModel->where(['order_type'=>5,'order_status'=>2])->sum('pay_price');//竞拍
                $info[][] = $this->orderModel->where(['refund_status'=>1] or ['refund_status'=>2])->sum('refund_amount');//退款
                foreach ($info as $k=> $v){
                    foreach($v as $key =>$value){
                        if(empty($value)){
                            $info[$k][$key] = 0;
                        }
                    }
                }
                $type = 'bar';
                break;
            case 1:
                $horizontal = ['0时','1时','2时','3时','4时','5时','6时','7时','8时','9时','10时','11时','12时',
                    '13时','14时','15时','16时','17时','18时','19时','20时','21时','22时','23时'];
                $order[0] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%h') as h,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_status',2)
                    ->whereTime("create_time","today")
                    ->group('h')
                    ->select();
                $order[1] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%h') as h,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',3)
                    ->whereTime("create_time","today")
                    ->group('h')
                    ->select();
                $order[2] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%h') as h,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',4)
                    ->whereTime("create_time","today")
                    ->group('h')
                    ->select()->toArray();
                $order[3] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%h') as h,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',5)
                    ->whereTime("create_time","today")
                    ->group('h')
                    ->select()->toArray();
                $order[4] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%h') as h,sum(refund_amount) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where(['refund_status'=>1] or ['refund_status'=>2])
                    ->whereTime("create_time","today")
                    ->group('h')
                    ->select()->toArray();
                $info = [0=>[],1=>[],2=>[],3=>[],4=>[]];
                foreach ($info as $k=>$v){
                    $i = 0;
                    while($i<count($horizontal)){
                        $info[$k][$i] = 0;
                        $i++;
                    }
                }
                foreach ($order as $k=>$v){
                    foreach ($v as $key=>$value){
                        $info[$k][(int)$value['h']] = $value['total'];
                    }
                }
                break;
            case 2:
                $horizontal = ['星期一','星期二','星期三','星期四','星期五','星期六','星期日'];
                if(date("w") == 0){
                    $i = 6;
                }else{
                    $i = date("w")-1;
                }
                $btime = strtotime(date('Y-m-d',time()).' days');
                $etime = strtotime(date('Y-m-d').'+1 days');
                while($i>=0){
                    $order[0][] = $this->orderModel->where('order_status',2)->where('finnshed_time','>',$btime)->where('finnshed_time','<',$etime)->sum('pay_price');
                    $order[1][] = $this->orderModel->where(['order_type'=>3])->where('finnshed_time','>',$btime)->where('finnshed_time','<',$etime)->sum('pay_price');//积分
                    $order[2][] = $this->orderModel->where(['order_type'=>4])->where('finnshed_time','>',$btime)->where('finnshed_time','<',$etime)->sum('pay_price');//集采
                    $order[3][] = $this->orderModel->where(['order_type'=>5])->where('finnshed_time','>',$btime)->where('finnshed_time','<',$etime)->sum('pay_price');//竞拍
                    $order[4][] = $this->orderModel->where(['refund_status'=>1] or ['refund_status'=>2])->where('refund_time','>',$btime)->where('refund_time','<',$etime)->sum('refund_amount');//退款
                    $i--;
                    $etime = $btime;
                    $btime = $btime-24*60*60;
                }
                $info = [0=>[],1=>[],2=>[],3=>[],4=>[]];
                foreach ($info as $k=>$v){
                    $i = 0;
                    while($i<count($horizontal)){
                        $info[$k][$i] = 0;
                        $i++;
                    }
                }
                foreach ($order as $k=>$v){
                    foreach ($v as $key=>$value){
                        if($value>0){
                            $info[$k][$key] = $value;
                        }
                    }
                }
                break;
            case 3:
                $btime = strtotime(date('Y-m'));
                $e_time = strtotime(date("Y-m").'+1 month');
                while($btime < $e_time){
                    $horizontal[] = date('m-d',$btime);
                    $btime+= 24*60*60;
                }
                $order[0] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%d') as days,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_status',2)
                    ->whereTime("create_time","month")
                    ->group('days')
                    ->select();
                $order[1] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%d') as days,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',3)
                    ->whereTime("create_time","month")
                    ->group('days')
                    ->select();
                $order[2] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%d') as days,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',4)
                    ->whereTime("create_time","month")
                    ->group('days')
                    ->select()->toArray();
                $order[3] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%d') as days,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',5)
                    ->whereTime("create_time","month")
                    ->group('days')
                    ->select()->toArray();
                $order[4] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%d') as days,sum(refund_amount) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where(['refund_status'=>1] or ['refund_status'=>2])
                    ->whereTime("create_time","month")
                    ->group('days')
                    ->select()->toArray();
                $info = [0=>[],1=>[],2=>[],3=>[],4=>[]];
                foreach ($info as $k=>$v){
                    $i = 0;
                    while($i<count($horizontal)){
                        $info[$k][$i] = 0;
                        $i++;
                    }
                }
                foreach ($order as $k=>$v){
                    foreach ($v as $key=>$value){
                        $info[$k][(int)$value['days']-1] = $value['total'];
                    }
                }
                break;
            case 4:
                $horizontal = ['1月','2月','3月','4月','5月','6月','7月','8月','9月','10月','11月','12月'];
                $order[0] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%m') as mo,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_status',2)
                    ->whereTime("create_time","year")
                    ->group('mo')
                    ->select();
                $order[1] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%m') as mo,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',3)
                    ->whereTime("create_time","year")
                    ->group('mo')
                    ->select();
                $order[2] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%m') as mo,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',4)
                    ->whereTime("create_time","year")
                    ->group('mo')
                    ->select()->toArray();
                $order[3] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%m') as mo,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',5)
                    ->whereTime("create_time","year")
                    ->group('mo')
                    ->select()->toArray();
                $order[4] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%m') as mo,sum(refund_amount) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where(['refund_status'=>1] or ['refund_status'=>2])
                    ->whereTime("create_time","year")
                    ->group('mo')
                    ->select()->toArray();
                $info = [0=>[],1=>[],2=>[],3=>[],4=>[]];
                foreach ($info as $k=>$v){
                    $i = 0;
                    while($i<count($horizontal)){
                        $info[$k][$i] = 0;
                        $i++;
                    }
                }
                foreach ($order as $k=>$v){
                    foreach ($v as $key=>$value){
                        $info[$k][(int)$value['mo']-1] = $value['total'];
                    }
                }
                break;
            case 5:
                $statr=strtotime($query['bdate']);
                $end=strtotime($query['edate'])+24*60*60-1;
                if((strtotime($query['edate'])-strtotime($query['bdate']))<24*60*60){
                    $type = 'bar';
                }
                $j=24*60*60;
                for ($i=$statr; $i <= $end ; $i+=$j) {
                    $day=date('Ymd',$i);
                    $horizontal[]=$day;
                }
                $order[0] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%Y%m%d') as d,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_status',2)
                    ->where("create_time","between",[$statr,$end])
                    ->group('d')
                    ->select();
                $order[1] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%Y%m%d') as d,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',3)
                    ->where("create_time","between",[$statr,$end])
                    ->group('d')
                    ->select();
                $order[2] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%Y%m%d') as d,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',4)
                    ->where("create_time","between",[$statr,$end])
                    ->group('d')
                    ->select()->toArray();
                $order[3] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%Y%m%d') as d,sum(pay_price) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where('order_type',5)
                    ->where("create_time","between",[$statr,$end])
                    ->group('d')
                    ->select()->toArray();
                $order[4] = Db::table('shop_order')
                    ->field("FROM_UNIXTIME(create_time,'%Y%m%d') as d,sum(refund_amount) as total")
                    ->where('site_id',$this->getSiteId())
                    ->where(['refund_status'=>1] or ['refund_status'=>2])
                    ->where("create_time","between",[$statr,$end])
                    ->group('d')
                    ->select()->toArray();
                $info = [0=>[],1=>[],2=>[],3=>[],4=>[]];
                foreach ($info as $k=>$v){
                    $i = 0;
                    while($i<count($horizontal)){
                        $info[$k][$i] = 0;
                        $i++;
                    }
                }
                foreach ($order as $k=>$v){
                    $a = array_flip($horizontal);
                    foreach ($v as $key=>$value){
                        if($a[$value['d']] !== null){
                            $info[$k][$a[$value['d']]] = $value['total'];
                        }
                    }
                }
                break;
        }
        $this->assign('type',json_encode($type));
        $this->assign('horizontal',json_encode(array_values($horizontal)));
        $this->assign('info',json_encode(array_values($info)));
        $query ['time_type'] = $timeType;
        $this->assign('query',$query);
        return $this->fetch();
    }

    /**
     * 会员趋势分析
     */
    public function user(){
        $year = $this->request->param('year',date('Y'));
        $statr=strtotime($year.'-01-01');
        $end=strtotime($year.'-12-31');
        $j=24*60*60;
        for ($i=$statr; $i <= $end ; $i+=$j) {
            $day=date('Ymd',$i);
            $dayData[]=$day;
        }
        //按天统计会员注册人数
        $user = Db::table('user')
            ->field("FROM_UNIXTIME(create_time,'%Y%m%d') as days,count(*) as total")
            ->where('utype','member')
            ->where('site_id',$this->getSiteId())
            ->where("FROM_UNIXTIME(create_time,'%Y')='{$year}'")
            ->group('days')
            ->select()
            ->toArray();
        $userStatic = [];
        foreach($user as $v){
            $userStatic[$v['days']] = $v['total'];
        }
        if(empty($userStatic)){
            $usersMax = 0;
        }else{
            $usersMax = max($userStatic)+2;
        }
        $order = Db::table('shop_order')
            ->field("FROM_UNIXTIME(create_time,'%Y%m%d') as days,sum(order_price) as total")
            ->where('site_id',$this->getSiteId())
            ->group('days')
            ->select();
        $orderStatic = [];
        foreach($order as $v){
            $orderStatic[$v['days']] = $v['total'];
        }
        if(empty($orderStatic)){
            $orderMax = 0;
        }else{
            $orderMax = max($orderStatic)+100;
        }
        foreach ($dayData as $v){
            if(!array_key_exists($v,$orderStatic)){
                $orderStatic[$v]=0;
            }
            if(!array_key_exists($v,$userStatic)){
                $userStatic[$v]=0;
            }
        }
        ksort($orderStatic);
        ksort($userStatic);
        $optionsData = [
            'time_data'=>$dayData,
            'title'=>['text'=>$year.'年会员注册下单趋势走向图'],
            'legend'=>['data'=>['下单金额','会员注册']],
            'yAxis'=>[
                ['max'=>$orderMax],
                ['max'=>$usersMax]
            ],
            'series'=>[
                ['data'=>array_values($orderStatic)],
                ['data'=>array_values($userStatic)],
            ],
        ];
        $this->assign('year',$year);
        $this->assign('optionsData',json_encode($optionsData));
        return $this->fetch();
    }

    /**
     * 商品销量排行
     */
    public function saleTop(){
        $query['sort_field'] = $this->request->param('sort_field','total');
        $query['area_id'] = $this->request->param('area_id','0-0');
        $query['date_type'] = $this->request->param('date_type','0');
        if($query['area_id']!='0-0'){
            $area = explode('-',$query['area_id']);
            if($area[1]==0){
                $map['prov_id'] = $area[0];
            }else{
                $map['city_id'] = $area[0];
            }
        }
        $saleTop = Db::table('shop_order_goods t1')
            ->join('shop_order t2','t1.order_id=t2.order_id')
            ->field('sku_id,goods_name,sku_name,sum(t1.goods_num) as total,sum(t1.pay_price) as total_price')
            ->where($map)
            ->group('sku_id')
            ->limit(100)
            ->order($query['sort_field'].' desc');
        if($query['date_type']>0){
            switch ($query['date_type']){
                case 1: //今日
                    $saleTop->whereTime('create_time','today');
                    break;
                case 2: //本周
                    $saleTop->whereTime('create_time','week');
                    break;
                case 3: //本月
                    $saleTop->whereTime('create_time','month');
                    break;
                case 4: //本年度
                    $saleTop->whereTime('create_time', 'year');
                    break;
                case 5: //昨日
                    $saleTop->whereTime('create_time','yesterday');
                    break;
                case 6: //上周
                    $saleTop->whereTime('create_time','last week');
                    break;
                case 7: //上月
                    $saleTop->whereTime('create_time','last month');
                    break;
                case 8: //去年
                    $saleTop->whereTime('create_time', 'last year');
                    break;
                default:
                    break;
            }
        }
        $saleTop = $saleTop->select();
        //echo Db::table('shop_order_goods')->getLastSql();
        $area = Db::table('area')->cache(true)->where('level<3')->order('area_sort')->select()->toArray();
        $area = TreeUtil::tree($area,'area_name');
        $this->assign('area',$area);
        $this->assign('query',$query);
        $this->assign('saleTop',$saleTop);
        return $this->fetch();
    }

}